package main

import (
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"time"

	//"gorm.io/driver/sqlite"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	conn, _ := sql.Open("mysql", "jscx:Jxdb#2020@tcp(172.29.49.241:3306)/hxchain?charset=utf8")
	//testDropTable(conn)
	//testCreateTable( conn)
	defer func() {
		_ = conn.Close()
		//随手关闭数据库是个好习惯
		log.Println("关闭数据库")
	}()

	//增 insert
	insertUserSql := "insert user(name,pwd) values (?,?)"
	fmt.Println("插入记录")
	_, _ = conn.Exec(insertUserSql, "tony1", "2021HaHa,.")
	_, _ = conn.Exec(insertUserSql, "tony2", "2021HaHa,.")
	res, _ := conn.Exec(insertUserSql, "tony2", "2021HaHa,.")
	affected, _ := res.RowsAffected()
	lastId, _ := res.LastInsertId()
	fmt.Printf("执行完成，插入%d行,最新ID：%d \n", affected, lastId)
	//改 update 更新操作获取lastInsertId无意义
	updateRes, err := conn.Exec("update user set pwd = ? where name = ?", getPWD(), "tony1")
	if err != nil {
		panic(err)
	}
	affected, _ = updateRes.RowsAffected()
	//lastId,_=updateRes.LastInsertId()
	fmt.Printf("执行完成，已更新%d行 \n", affected)

	//删除delete
	deleteRes, _ := conn.Exec("delete from user where name= ?", "tony0")
	rowsAffected, _ := deleteRes.RowsAffected()
	fmt.Printf("执行完成，已删除%d行 \n", rowsAffected)

	//查 select
	//查询一行数据
	row := conn.QueryRow("select * from user where uid=?", 1)
	var (
		name, pwd string
		uid       uint
	)
	_ = row.Scan(&uid, &name, &pwd)
	fmt.Println("查找id=10的数据：", uid, ", ", name, "------", pwd)
	//查询多行数据
	rows, _ := conn.Query("select * from user where name=?", "tony2")
	for rows.Next() {
		_ = rows.Scan(&uid, &name, &pwd)
		fmt.Println("查找name='tony2'的数据集合：", uid, ", ", name, "------", pwd)
	}
	//test_sqlite()
}
func getPWD() string {
	//真随机数需要加入时间戳作为种子
	rand.Seed(time.Now().UnixNano())
	return fmt.Sprintf("GLhx,%d", rand.Intn(100)+2020)
}
func testDropTable(conn *sql.DB) {
	_, _ = conn.Exec("drop table user")
}

func testCreateTable(conn *sql.DB) {
	fmt.Println("创建user表user(name VARCHAR(40),pwd VARCHAR(40))")
	createTableSql := "create table user(uid int(4)primary key not null auto_increment,name VARCHAR(40),pwd VARCHAR(40))"
	_, err := conn.Exec(createTableSql)
	if err != nil {
		log.Panicln("创建表发生错误：", err)
	} else {
		fmt.Println("创建表成功")
	}
}

//func test_sqlite() {
//	db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
//	if err != nil {
//		panic("failed to connect database")
//	}
//
//	// 迁移 schema
//	err = db.AutoMigrate(&Product{})
//	if err != nil {
//		return
//	}
//
//	// Create
//	db.Create(&Product{Code: "D42", Price: 100})
//
//	// Read
//	var product Product
//	db.First(&product, 1)                 // 根据整形主键查找
//	db.First(&product, "code = ?", "D42") // 查找 code 字段值为 D42 的记录
//
//	// Update - 将 product 的 price 更新为 200
//	db.Model(&product).Update("Price", 200)
//	// Update - 更新多个字段
//	db.Model(&product).Updates(Product{Price: 200, Code: "F42"}) // 仅更新非零值字段
//	db.Model(&product).Updates(map[string]interface{}{"Price": 200, "Code": "F42"})
//
//	// Delete - 删除 product
//	db.Delete(&product, 1)
//
//}
